﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DataAccessLayer.Properties;
using DataAccessLayer.Admin.Classes;
using DataAccessLayer.Admin.Cache;
using System.Linq.Expressions;
using DataAccessLayer.Admin.Filters;
using IMModel.Domain;
using System.Diagnostics;
using System.Transactions;

namespace DataAccessLayer
{
    public partial class SqlDataProvider : IDataProvider
    {
        public IEnumerable<DTO.AdmJob> GetAdmJobs()
        {
            using (IMModel.Domain.IMDataDataContext db = new IMModel.Domain.IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                var q = from j in db.Jobs
                        join p in db.Positions on j.IDPosition equals p.IDPosition
                        join s in db.Status on j.IDStatus equals s.IDStatus
                        orderby p.Name, s.Order
                        select j;
                foreach (var j in q)
                {
                    yield return DTOConverter.Instance.CreateAdmJob(j);
                }
            }
        }

        public IEnumerable<DTO.AdmPersonDegree> GetAdmPersonDegrees(int IDTerm)
        {
            using (IMModel.Domain.IMDataDataContext db = new IMModel.Domain.IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                var q = from p in db.PersonDegrees
                        where p.IDTerm == IDTerm
                        orderby p.Person.LastName, p.Person.FirstName, p.Person.Patronymic, p.Degree.Name
                        select p;
                foreach (var p in q)
                {
                    yield return DTOConverter.Instance.CreateAdmPersonDegree(p);
                }
            }
        }

        public IEnumerable<DTO.AdmDegree> GetAdmDegrees()
        {
            using (IMModel.Domain.IMDataDataContext db = new IMModel.Domain.IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                var q = from d in db.Degrees orderby d.Name select d;
                foreach (var d in q)
                {
                    yield return DTOConverter.Instance.CreateAdmDegree(d);
                }
            }
        }

        public IEnumerable<DTO.AdmPerson> GetAdmPersons()
        {
            using (IMModel.Domain.IMDataDataContext db = new IMModel.Domain.IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                var q = from p in db.Persons orderby p.LastName, p.FirstName, p.Patronymic select p;
                foreach (var P in q)
                {
                    yield return DTOConverter.Instance.CreateAdmPerson(P);
                }
            }
        }

        public IEnumerable<DTO.AdmTerm> GetAdmTerms()
        {
            using (IMModel.Domain.IMDataDataContext db = new IMModel.Domain.IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                var q = from t in db.Terms orderby t.Order select t;
                foreach (var t in db.Terms)
                {
                    yield return DTOConverter.Instance.CreateAdmTerm(t);
                }
            }
        }

        public IEnumerable<DTO.AdmPosition> GetAdmPositions()
        {
            using (IMModel.Domain.IMDataDataContext db = new IMModel.Domain.IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                var q = from p in db.Positions orderby p.Name select p;
                foreach (var p in q)
                {
                    yield return DTOConverter.Instance.CreateAdmPosition(p);
                }
            }
        }

        public IEnumerable<DTO.AdmStatus> GetAdmStatuses()
        {
            using (IMModel.Domain.IMDataDataContext db = new IMModel.Domain.IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                var q = from s in db.Status orderby s.Order select s;
                foreach (var s in q)
                {
                    yield return DTOConverter.Instance.CreateAdmStatus(s);
                }
            }
        }

        public IEnumerable<DTO.AdmUnit> GetAdmUnits()
        {
            using (IMModel.Domain.IMDataDataContext db = new IMModel.Domain.IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                var q = from u in db.Units orderby u.Name select u;
                foreach (var u in q)
                {
                    yield return DTOConverter.Instance.CreateAdmUnit(u, true);
                }
            }
        }

        public IEnumerable<DTO.AdmResponsible> GetAdmResponsibles()
        {
            using (IMModel.Domain.IMDataDataContext db = new IMModel.Domain.IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                var q = from r in db.Responsibles
                        join p in db.Positions on r.IDPosition equals p.IDPosition
                        join d in db.Departments on r.IDDepartment equals d.IDDepartment into d_def
                        from d in d_def.DefaultIfEmpty()
                        orderby p.Name, d.Name
                        select r;
                foreach (Responsible r in q)
                {
                    yield return DTOConverter.Instance.CreateAdmResponsible(r);
                }
            }
        }

        public IEnumerable<DTO.AdmCharacteristic> GetAdmCharacteristics()
        {
            using (IMModel.Domain.IMDataDataContext db = new IMModel.Domain.IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                foreach (var c in db.Characteristics.AsEnumerable().OrderBy(o => o.Name, new NatSort<string>()))
                {
                    yield return DTOConverter.Instance.CreateAdmCharacteristic(c);
                }
            }
        }

        public IEnumerable<DTO.AdmGradation> GetAdmGradations()
        {
            using (IMModel.Domain.IMDataDataContext db = new IMModel.Domain.IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                var q = from g in db.Gradations orderby g.Name select g;
                foreach (var g in q)
                {
                    yield return DTOConverter.Instance.CreateAdmGradation(g);
                }
            }
        }

        public IEnumerable<DTO.AdmCategory> GetAdmCategories()
        {
            using (IMModel.Domain.IMDataDataContext db = new IMModel.Domain.IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                var q = from c in db.Categories orderby c.Name select c;
                foreach (var c in q)
                {
                    yield return DTOConverter.Instance.CreateAdmCategory(c);
                }
            }
        }

        public IEnumerable<DTO.AdmDepartment> GetAdmDepartments(AdmDepartmentSortMode sortMode)
        {
            using (IMModel.Domain.IMDataDataContext db = new IMModel.Domain.IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                List<DTO.AdmDepartment> tmp = new List<DTO.AdmDepartment>();
                var q = from d in db.Departments select d;
                foreach (var d in q)
                {
                    tmp.Add(DTOConverter.Instance.CreateAdmDepartment(d));
                }
                switch (sortMode)
                {
                    case AdmDepartmentSortMode.FullName:
                        return tmp.OrderBy(o => o.FullName);
                    case AdmDepartmentSortMode.TypeAndName:
                        return tmp.OrderBy(o => o.DepartmentType.Name).ThenBy(t => t.Name.Replace("{0}", string.Empty));
                    default:
                        return tmp;
                }
            }
        }

        public IEnumerable<DTO.AdmSection> GetAdmSections()
        {
            using (IMModel.Domain.IMDataDataContext db = new IMModel.Domain.IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                foreach (var s in db.Sections.AsEnumerable().OrderBy(o => o.Name))
                {
                    yield return DTOConverter.Instance.CreateAdmSection(s);
                }
            }
        }

        /// <summary>
        /// Категории + веса
        /// </summary>
        /// <param name="IDTerm"></param>
        /// <returns></returns>
        public IEnumerable<DTO.AdmCategory> GetAdmCategoriesByTerm(int IDTerm)
        {
            using (IMModel.Domain.IMDataDataContext db = new IMModel.Domain.IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                var q = from c in db.Categories
                        join ct in db.CategoryTerms on c.IDCategory equals ct.IDCategory
                        where ct.IDTerm == IDTerm
                        orderby c.Name
                        select c;
                foreach (var c in q)
                {
                    yield return DTOConverter.Instance.CreateAdmCategory(c, IDTerm);
                }
            }
        }

        /// <summary>
        /// Градации + веса
        /// </summary>
        /// <param name="IDTerm"></param>
        /// <returns></returns>
        public IEnumerable<DTO.AdmGradation> GetGradationsStruct(int IDTerm)
        {
            using (IMModel.Domain.IMDataDataContext db = new IMModel.Domain.IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                List<DTO.AdmGradation> list = new List<DTO.AdmGradation>();

                var gradationRelations = db.GradationTerms.Where(w => w.IDTerm == IDTerm).AsEnumerable().OrderBy(o => o.Order, new NatSort<string>());

                foreach (GradationTerm gr in gradationRelations)
                {
                    list.Add(DTOConverter.Instance.CreateAdmGradation(gr));
                }
                foreach (DTO.AdmGradation grad in list)
                {
                    foreach (DTO.AdmGradation g in list.Where(w => w.IDParentGradationTerm == grad.IDGradationTerm).AsEnumerable().OrderBy(o => o.Weight))
                    {
                        g.ParentGradation = grad;
                        (grad.Childs as List<DTO.DTOBase>).Add(g);
                    }
                }
                IEnumerable<DTO.AdmGradation> tmp = list.Where(w => w.IDParentGradationTerm == null);
                return tmp;
            }
        }

        public IEnumerable<DTO.DTOBase> GetGradationsStructOnDemand(int IDTerm, DTO.AdmGradation fromGradation)
        {
            using (IMModel.Domain.IMDataDataContext db = new IMModel.Domain.IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                if (fromGradation != null && fromGradation.State != DTO.StateObject.Unchanged && fromGradation.State != DTO.StateObject.Updated)
                {
                    throw new Exception("GetAdmGradationsStructOnDemand: состояние объекта fromGradation отлично от Unchanged и Updated");
                }

                int? IDParentGradationRelation = (fromGradation == null) ? null : (int?)fromGradation.IDGradationTerm;

                var gradationRelations = from gr in db.GradationTerms
                                         where gr.IDTerm == IDTerm && ((gr.IDParentGradationTerm == null
                                            && !IDParentGradationRelation.HasValue) || (gr.IDParentGradationTerm == IDParentGradationRelation && IDParentGradationRelation.HasValue))
                                         select gr;

                IEnumerable<GradationTerm> sortedGradationRelations = null;
                // Для корневых градаций сортируем по Order, для остальных по Weight
                if (fromGradation == null)
                    sortedGradationRelations = gradationRelations.AsEnumerable().OrderBy(o => o.Order, new NatSort<string>());
                else
                    sortedGradationRelations = gradationRelations.AsEnumerable().OrderBy(o => o.Weight).ThenBy(t => t.Order);

                foreach (GradationTerm gr in sortedGradationRelations)
                {
                    DTO.AdmGradation gradation = DTOConverter.Instance.CreateAdmGradation(gr);
                    gradation.ParentGradation = fromGradation;

                    gradation.ChildsCount = db.GradationTerms.Where(w => w.IDParentGradationTerm == gr.IDGradationTerm && w.IDTerm == IDTerm).Count();
                    //gradation.ChildsCount = (gr.PreCalcChildrenCount ?? 0);
                    yield return gradation;
                }
            }
        }

        /// <summary>
        /// Возвращает содержимое раздела
        /// </summary>
        /// <param name="IDTerm"></param>
        /// <param name="fromSection">Свойство IDSectionTerm должно быть обязательно заполнено. Объект c состоянием Created и Deleted недопустим</param>
        /// <returns></returns>
        public IEnumerable<DTO.DTOBase> GetIndexMarkStructOnDemand(int IDTerm, DTO.AdmSection fromSection, bool WithCharacteristics)
        {
            using (IMModel.Domain.IMDataDataContext db = new IMModel.Domain.IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                if (fromSection != null && fromSection.State != DTO.StateObject.Unchanged && fromSection.State != DTO.StateObject.Updated)
                {
                    throw new Exception("GetAdmFullSections: состояние объекта fromSection отлично от Unchanged и Updated");
                }
                if (fromSection != null && fromSection.IDSectionTerm == 0)
                {
                    throw new Exception("GetAdmFullSections: некорректное значение IDSectionTerm у объекта fromSection");
                }

                GetIndexMarkStructCache cache = new GetIndexMarkStructCache();

                int? IDParentSectionTerm = (fromSection == null) ? null : (int?)fromSection.IDSectionTerm;

                var sections = from s in db.Sections
                               join st in db.SectionTerms on s.IDSection equals st.IDSection
                               where st.IDTerm == IDTerm && ((st.IDParentSectionTerm == null && !IDParentSectionTerm.HasValue) || (st.IDParentSectionTerm == IDParentSectionTerm && IDParentSectionTerm.HasValue))
                               select new { s, st };

                foreach (var s in sections.AsEnumerable().OrderBy(o => o.st.Order, new NatSort<string>()))
                {
                    DTO.AdmSection section = DTOConverter.Instance.CreateAdmSection(s.s, IDTerm);
                    section.ParentSection = fromSection;

                    section.SectionsCount = db.SectionTerms.Where(w => w.IDParentSectionTerm == section.IDSectionTerm && w.IDTerm == IDTerm).Count();
                    if (WithCharacteristics == true)
                    {
                        section.CharacteristicsCount = db.CharacteristicTerms.Where(w => w.CategorySectionTerm.IDSectionTerm == s.st.IDSectionTerm && w.IDTerm == IDTerm).Count();
                    }
                    section.ChildsCount = section.SectionsCount + section.CharacteristicsCount;
                    yield return section;
                }

                if (fromSection != null && WithCharacteristics == true)
                {

                    var characteristics = (from c in db.Characteristics
                                           join ct in db.CharacteristicTerms on c.IDCharacteristic equals ct.IDCharacteristic
                                           where ct.IDTerm == IDTerm && ct.CategorySectionTerm.IDSectionTerm == fromSection.IDSectionTerm
                                           select new { c, ct }).Distinct();

                    foreach (var ch in characteristics.AsEnumerable().OrderBy(o => o.ct.Order, new NatSort<string>()))
                    {
                        DTO.AdmCharacteristic characteristic = DTOConverter.Instance.CreateAdmCharacteristic(cache, ch.c, ch.ct);
                        characteristic.Section = fromSection;

                        characteristic.ChildsCount += db.CharacteristicGroups.Where(w => w.IDCharacteristicTerm == ch.ct.IDCharacteristicTerm).Count();

                        yield return characteristic;
                    }
                }
            }
        }

        public IEnumerable<DTO.DTOBase> GetIndexMarkStructOnDemand(int IDTerm, DTO.AdmCharacteristic characteristic)
        {
            using (IMModel.Domain.IMDataDataContext db = new IMModel.Domain.IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                var gradationRelations = from cg in db.CharacteristicGroups
                                          join gr in db.GradationTerms on cg.IDGradationGroupTerm equals gr.IDGradationTerm
                                          where cg.IDCharacteristicTerm == characteristic.IDCharacteristicTerm && gr.IDTerm == IDTerm
                                          select gr;

                foreach (DTO.AdmGradation gradation in GetGradationsHierarchical(gradationRelations).OrderBy(o => o.Order, new NatSort<string>()))
                {
                    gradation.IsNecessary = db.CharacteristicGroups.Single(x => x.IDCharacteristicTerm == characteristic.IDCharacteristicTerm
                            && x.IDGradationGroupTerm == gradation.IDGradationTerm).IsNecessary;
                    gradation.ParentCharacteristic = characteristic;
                    yield return gradation;
                }
            }
        }

        private static IEnumerable<DTO.AdmGradation> GetGradationsHierarchical(IQueryable<GradationTerm> gradationRelations)
        {
            Func<IEnumerable<GradationTerm>, IEnumerable<DTO.AdmGradation>> func = null;
            func = (IEnumerable<GradationTerm> gradationTerms) =>
            {
                List<DTO.AdmGradation> res = new List<DTO.AdmGradation>();
                foreach (GradationTerm gradation in gradationTerms)
                {
                    DTO.AdmGradation gt = DTOConverter.Instance.CreateAdmGradation(gradation);

                    (gt.Childs as List<DTO.DTOBase>).AddRange(func(gradation.GradationTerms).OrderBy(o => o.Order, new NatSort<string>()));
                    res.Add(gt);
                }
                return res;
            };
            return func(gradationRelations);
        }

        public IEnumerable<DTO.DTOBase> GetUniversityStructOnDemand(int IDTerm, DTO.AdmDepartment fromDepartment, bool WithRespondents)
        {
            using (IMModel.Domain.IMDataDataContext db = new IMModel.Domain.IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                if (fromDepartment != null && fromDepartment.State != DTO.StateObject.Unchanged && fromDepartment.State != DTO.StateObject.Updated)
                {
                    throw new Exception("GetAdmFullDepartmentsOnDemand: состояние объекта fromDepartment отлично от Unchanged и Updated");
                }

                int? IDParentDepartmentTerm = (fromDepartment == null) ? null : (int?)fromDepartment.IDDepartmentTerm;

                var query = from d in db.Departments
                            join dt in db.DepartmentTerms on d.IDDepartment equals dt.IDDepartment
                            where dt.IDTerm == IDTerm && ((dt.IDParentDepartmentTerm == null
                                && !IDParentDepartmentTerm.HasValue) || (dt.IDParentDepartmentTerm == IDParentDepartmentTerm && IDParentDepartmentTerm.HasValue))
                            orderby d.Name.Replace("{0}", string.Empty).Trim()
                            select new { d, dt };

                foreach (var dep in query)
                {
                    DTO.AdmDepartment department = DTOConverter.Instance.CreateAdmDepartment(dep.d, dep.dt);
                    department.ParentDepartment = fromDepartment;

                    department.ChildsCount += db.DepartmentTerms.Where(w => w.IDParentDepartmentTerm == department.IDDepartmentTerm).Count();
                    if (WithRespondents == true)
                    {
                        department.ChildsCount += db.Respondents.Where(w => w.IDDepartmentTerm == department.IDDepartmentTerm).Count();
                    }
                    yield return department;
                }

                if (fromDepartment != null && WithRespondents == true)
                {
                    var resps = from res in db.Respondents
                                join per in db.Persons on res.IDPerson equals per.IDPerson
                                where res.IDDepartmentTerm == fromDepartment.IDDepartmentTerm
                                orderby per.LastName, per.LastName, per.Patronymic
                                select res;

                    int i = 0;
                    foreach (var r in resps)
                    {
                        DTO.AdmRespondent respondent = DTOConverter.Instance.CreateAdmRespondent(r);
                        respondent.Department = fromDepartment;

                        respondent.Order = ++i;
                        yield return respondent;
                    }
                }
            }
        }

        public IEnumerable<DTO.AdmAnswerLog> GetAnswerLog(DateTime fromDate, DateTime toDate, List<DTO.FilterItem> filters,
            bool onlySubscribers)
        {
            using (IMModel.Domain.IMDataDataContext db = new IMModel.Domain.IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                IEnumerable<Answer> q = null;
                if (onlySubscribers == false)
                {
                    q = from a in db.Answers
                        where a.DateTime.Value.Date >= fromDate.Date && a.DateTime.Value.Date <= toDate.Date
                        orderby a.DateTime descending
                        select a;
                }
                else
                {
                    //q = from a in db.Answers
                    //    join r in db.Respondents on a.IDRespondent equals r.IDRespondent
                    //    where a.DateTime.Value.Date >= fromDate.Date && a.DateTime.Value.Date <= toDate.Date
                    //        && r.Person.E_Mail.Trim() != string.Empty && r.Person.E_Mail != null
                    //    orderby a.DateTime descending
                    //    select a;
                    q = (from answers in db.Answers
                        join subscribe in db.Subscribes on answers.IDRespondent equals subscribe.IDRespondent
                        join subscribers in db.Subscribers on subscribe.IDSubscriber equals subscribers.IDSubscriber
                        where subscribers.IsActive == true &&
                            answers.DateTime.Value.Date >= fromDate.Date && answers.DateTime.Value.Date <= toDate.Date
                        select answers).Distinct();
                }

                if (filters == null) filters = new List<DTO.FilterItem>();

                foreach (DTO.FilterItem filterItem in filters)
                {
                    if (!(filterItem.SelectedAttibute is DTO.FilterAnswerLogAttributes)) continue;

                    switch ((DTO.FilterAnswerLogAttributes)filterItem.SelectedAttibute)
                    {
                        case DTO.FilterAnswerLogAttributes.Person:
                            q = q.Where(AnswerLogFilterGenerator.I.Person(filterItem.Condition, filterItem.ConditionType));
                            break;
                        case DTO.FilterAnswerLogAttributes.CharacteristicName:
                            q = q.Where(AnswerLogFilterGenerator.I.Characteristic(filterItem.Condition, filterItem.ConditionType));
                            break;
                        case DTO.FilterAnswerLogAttributes.Department:
                            q = q.Where(AnswerLogFilterGenerator.I.Department(filterItem.Condition, filterItem.ConditionType));
                            break;
                        case DTO.FilterAnswerLogAttributes.User:
                            q = q.Where(AnswerLogFilterGenerator.I.UserName(filterItem.Condition, filterItem.ConditionType));
                            break;
                        case DTO.FilterAnswerLogAttributes.Operation:
                            q = q.Where(AnswerLogFilterGenerator.I.Operation(filterItem.Condition, filterItem.ConditionType));
                            break;
                        case DTO.FilterAnswerLogAttributes.Volume:
                            q = q.Where(AnswerLogFilterGenerator.I.Volume(filterItem.ConditionAsDouble, filterItem.ConditionType));
                            break;
                        default:
                            throw new Exception("Данный фильтр не реализован");
                    }
                }

                GetAnswerLogCache cache = new GetAnswerLogCache();
                foreach (Answer a in q)
                {
                    yield return DTOConverter.Instance.CreateAdmAnswerLog(cache, a);
                }
            }
        }

        public IEnumerable<DTO.AdmAnswerLog> GetAnswerLog()
        {
            using (IMModel.Domain.IMDataDataContext db = new IMModel.Domain.IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                DateTime? lastDate = db.Answers.Max(m => m.DateTime);
                if (lastDate == null)
                {
                    lastDate = DateTime.Now;
                }

                IEnumerable<Answer> q = (from a in db.Answers
                                            where a.DateTime.Value.Date == lastDate.Value.Date
                                            orderby a.DateTime
                                            descending
                                            select a);

                GetAnswerLogCache cache = new GetAnswerLogCache();
                foreach (Answer a in q)
                {
                    yield return DTOConverter.Instance.CreateAdmAnswerLog(cache, a);
                }
            }
        }

        public IEnumerable<DTO.AdmAnswerLog> GetAnswerLog(int IDAnswer)
        {
            using (IMModel.Domain.IMDataDataContext db = new IMModel.Domain.IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                var answer = db.Answers.Single(x => x.IDAnswer == IDAnswer);

                GetAnswerLogCache cache = new GetAnswerLogCache();
                DTO.AdmAnswerLog a = DTOConverter.Instance.CreateAdmAnswerLog(cache, answer);

                if (answer.IDOldAnswer != null)
                {
                    int? IDOldAnswer = answer.IDOldAnswer;
                    a.Childs = new List<DTO.DTOBase>();
                    while (IDOldAnswer != null)
                    {
                        var dbAns = answer.Answer1;
                        (a.Childs as List<DTO.DTOBase>).Add(DTOConverter.Instance.CreateAdmAnswerLog(cache, dbAns));
                        IDOldAnswer = dbAns.IDOldAnswer;
                    }
                }

                return new List<DTO.AdmAnswerLog> { a };
            }
        }

        public IEnumerable<DTO.AdmInputLog> GetInputLog(DateTime fromDate, DateTime toDate, List<DTO.FilterItem> filters)
        {
            using (IMModel.Domain.IMDataDataContext db = new IMModel.Domain.IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                IEnumerable<InputLog> q = null;

                q = from a in db.InputLogs
                        where a.Date.Date >= fromDate.Date && a.Date.Date <= toDate.Date
                        orderby a.Date descending
                        select a;

                foreach (DTO.FilterItem filterItem in filters)
                {
                    if (!(filterItem.SelectedAttibute is DTO.FilterInputLogAttributes)) continue;

                    switch ((DTO.FilterInputLogAttributes)filterItem.SelectedAttibute)
                    {
                        case DTO.FilterInputLogAttributes.User:
                            q = q.Where(InputLogFilterGenerator.I.UserName(filterItem.Condition.ToString(), filterItem.ConditionType));
                            break;
                        default:
                            throw new Exception("Данный фильтр не реализован");
                    }
                }

                foreach (InputLog il in q)
                {
                    yield return DTOConverter.Instance.CreateAdmInputLog(il);
                }
            }
        }

        public IEnumerable<DTO.AdmInputLog> GetInputLog()
        {
            using (IMModel.Domain.IMDataDataContext db = new IMModel.Domain.IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                DateTime lastDate = db.InputLogs.Max(m => m.Date);

                var q = (from l in db.InputLogs 
                         where l.Date.Date == lastDate.Date
                         orderby l.Date 
                         descending select l);
                foreach (InputLog il in q)
                {
                    yield return DTOConverter.Instance.CreateAdmInputLog(il);
                }
            }
        }

        public static void GetTrace(List<GradationTerm> trace, int? IDRelation, int IDTerm)
        {
            // Показатель может быть неградуированным
            if (IDRelation == null) return;
            using (IMModel.Domain.IMDataDataContext db = new IMModel.Domain.IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                GradationTerm gradationrelation = db.GradationTerms.FirstOrDefault(f => f.IDGradationTerm == IDRelation && f.IDTerm == IDTerm);
                if (gradationrelation == null)
                {
                    throw new Exception("Одна из записей ссылается на связь, имеющую некоректный период");
                }
                if (trace.Where(w => w.IDGradationTerm == gradationrelation.IDGradationTerm).Count() == 0) trace.Add(gradationrelation);
                if (gradationrelation.IDParentGradationTerm != null) GetTrace(trace, gradationrelation.IDParentGradationTerm, IDTerm);
            }
        }

        private IQueryable<Responsible> GetResponsibles(int IDCharacteristic, int IDTerm)
        {
            using (IMModel.Domain.IMDataDataContext db = new IMModel.Domain.IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                return from ct in db.CharacteristicTerms
                       join cr in db.CharacteristicResponsibles on ct.IDCharacteristicTerm equals cr.IDCharacteristicTerm
                       join r in db.Responsibles on cr.IDResponsible equals r.IDResponsible
                       where ct.IDCharacteristic == IDCharacteristic && ct.IDTerm == IDTerm
                       select r;
            }
        }

        public IEnumerable<DTO.AdmDepartmentType> GetAdmDepartmentTypes()
        {
            using (IMModel.Domain.IMDataDataContext db = new IMModel.Domain.IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                foreach (DepartmentType dt in db.DepartmentTypes.Select(s => s))
                {
                    DTO.AdmDepartmentType admDepType = DTOConverter.Instance.CreateAdmDepartmentType(dt);
                    admDepType.DepartmentsCount = db.Departments.Where(w => w.IDDepartmentType == dt.ID).Count();
                    yield return admDepType;
                }
            }
        }

        public int GetIDCategorySectionTerm(int IDSectionTerm, int IDCategoryTerm)
        {
            using (IMModel.Domain.IMDataDataContext db = new IMModel.Domain.IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                CategorySectionTerm cst = db.CategorySectionTerms.SingleOrDefault(s => s.IDSectionTerm == IDSectionTerm
                    && s.IDCategoryTerm == IDCategoryTerm);
                if (cst != null)
                {
                    return cst.ID;
                }
                else
                {
                    CategorySectionTerm new_cst = new CategorySectionTerm
                    {
                        IDSectionTerm = IDSectionTerm,
                        IDCategoryTerm = IDCategoryTerm
                    };
                    db.CategorySectionTerms.InsertOnSubmit(new_cst);
                    db.SubmitChanges();
                    return new_cst.ID;
                }
            }
        }

        private IEnumerable<DTO.AdmGradation> GetBottomGradations(IEnumerable<DTO.DTOBase> from)
        {
            IEnumerable<DTO.AdmGradation> _from = from.Where(w => w.GetType() == typeof(DTO.AdmGradation)).Cast<DTO.AdmGradation>();
            foreach (DTO.AdmGradation grad in _from)
            {
                if (grad.Childs.Count() == 0)
                {
                    yield return grad;
                }
                else
                {
                    foreach (DTO.AdmGradation g in GetBottomGradations(grad.Childs.Cast<DTO.DTOBase>()))
                    {
                        yield return g;
                    }
                }
            }
        }

        /// <summary>
        /// Получение флага наличия подписки
        /// </summary>
        /// <param name="SID"></param>
        /// <returns></returns>
        public DTO.SubscriberState CheckSubscribe(string SID)
        {
            using (IMModel.Domain.IMDataDataContext db = new IMModel.Domain.IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                Subscriber subs = db.Subscribers.SingleOrDefault(x => x.SID == SID);
                if (subs == null)
                    return DTO.SubscriberState.NotExist;
                else
                {
                    if (subs.IsActive == true)
                        return DTO.SubscriberState.Activate;
                    else
                        return DTO.SubscriberState.NotActivate;
                }
            }
        }

        /// <summary>
        /// Получение данных о подписке
        /// </summary>
        /// <param name="SID"></param>
        /// <returns></returns>
        public DTO.SubscriberInfo GetSubscriberInfo(string SID)
        {
            using (IMModel.Domain.IMDataDataContext db = new IMModel.Domain.IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                Subscriber subs = db.Subscribers.Single(x => x.SID == SID);

                return DTOConverter.Instance.CreateSubscriberInfo(subs);
            }
        }

        public IEnumerable<DTO.SubscriberInfo> GetAllActivitySubscribers(DateTime fromDate, DateTime toDate)
        {
            using (IMModel.Domain.IMDataDataContext db = new IMModel.Domain.IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                var q = (from answers in db.Answers
                         join subscribe in db.Subscribes on answers.IDRespondent equals subscribe.IDRespondent
                         join subscribers in db.Subscribers on subscribe.IDSubscriber equals subscribers.IDSubscriber
                         where subscribers.IsActive == true &&
                             answers.DateTime.Value.Date >= fromDate.Date && answers.DateTime.Value.Date <= toDate.Date
                         select subscribers).Distinct();

                foreach (var subs in q)
                {
                    yield return DTOConverter.Instance.CreateSubscriberInfo(subs);
                }
            }
        }

        /// <summary>
        /// Сохранение данных о подписке
        /// </summary>
        /// <param name="subscriberInfo"></param>
        public void SaveSubscriberInfo(DTO.SubscriberInfo subscriberInfo)
        {
            using (IMModel.Domain.IMDataDataContext db = new IMModel.Domain.IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                if (subscriberInfo.State == DTO.StateObject.Created)
                {
                    Subscriber subscriber = new Subscriber();
                    subscriber.SID = subscriberInfo.SID;
                    subscriber.Email = subscriberInfo.Email;
                    subscriber.IsActive = subscriberInfo.IsActive;

                    db.Subscribers.InsertOnSubmit(subscriber);

                    foreach (DTO.Respondent respondent in subscriberInfo.Respondents)
                    {
                        db.Subscribes.InsertOnSubmit(new Subscribe
                        {
                            Subscriber = subscriber,
                            IDRespondent = respondent.IDRespondent
                        });
                    }

                    db.SubmitChanges();
                }

                if (subscriberInfo.State == DTO.StateObject.Updated)
                {
                    using (var transaction = new TransactionScope())
                    {
                        Subscriber subscriber = db.Subscribers.Single(x => x.IDSubscriber == subscriberInfo.IDSubscriber);
                        subscriber.Email = subscriberInfo.Email;
                        subscriber.IsActive = subscriberInfo.IsActive;

                        db.Subscribes.DeleteAllOnSubmit(db.Subscribes.Where(x => x.IDSubscriber == subscriber.IDSubscriber));
                        db.SubmitChanges();

                        foreach (DTO.Respondent respondent in subscriberInfo.Respondents)
                        {
                            db.Subscribes.InsertOnSubmit(new Subscribe
                            {
                                IDSubscriber = subscriber.IDSubscriber,
                                IDRespondent = respondent.IDRespondent
                            });
                        }

                        db.SubmitChanges();

                        transaction.Complete();
                    }
                }

            }
        }
    }
}
